package org.budo.mongo.jdbc.driver.parser.jsqlparser;

import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.budo.jdbc.driver.exception.BudoJdbcDriverNotSupportedException;
import org.budo.jdbc.driver.statement.JdbcStatement;
import org.budo.mongo.jdbc.driver.BudoMongoJdbcResultSetMetaData;
import org.budo.mongo.jdbc.driver.statement.MongoStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoAggregateStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoCountStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoDeleteStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoEvalFindStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoFindStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoInsertStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoUpdateStatement;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_CREATE_TABLE;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_DATABASES;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_FULL_TABLES;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_TABLES;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_TABLE_STATUS;
import org.budo.support.lang.util.ListUtil;
import org.budo.support.lang.util.MapUtil;
import org.budo.support.lang.util.UuidUtil;

import com.mongodb.QueryOperators;
import com.mongodb.util.JSON;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.update.Update;

/**
 * @author li
 */
public class JSqlParserMongoStatementParser extends AbstractJSqlParserMongoStatementParser {
    @Override
    public JdbcStatement parse(String sql) throws SQLException {
        String _sql = sql.trim().toUpperCase(); // 转大写
        if (_sql.startsWith("SHOW DATABASES")) {
            return new SHOW_DATABASES();
        }

        if (_sql.startsWith("SHOW TABLE STATUS")) {
            return new SHOW_TABLE_STATUS();
        }

        if (_sql.startsWith("SHOW FULL TABLES")) {
            return new SHOW_FULL_TABLES();
        }

        if (_sql.startsWith("SHOW CREATE TABLE")) {
            String tableName = MongoJSqlParserUtil.getTableNameWhenShow(sql);
            return new SHOW_CREATE_TABLE() //
                    .setTableName(tableName);
        }

        return super.parse(sql); // 解析其他语句
    }

    protected JdbcStatement doParseSql(String sql) throws JSQLParserException {
        String _sql = sql.trim().toUpperCase();
        if ("SHOW TABLES".equalsIgnoreCase(_sql)) {
            return new SHOW_TABLES();
        }

        Statement statement = CCJSqlParserUtil.parse(sql);

        if (statement instanceof Insert) {
            return this.parseInsert(sql, (Insert) statement);
        }

        if (statement instanceof Update) {
            return this.parseUpdate(sql, (Update) statement);
        }

        if (statement instanceof Delete) {
            return this.parseDelete(sql, (Delete) statement);
        }

        if (statement instanceof Select) {
            return this.parseSelect(sql, statement);
        }

        throw new BudoJdbcDriverNotSupportedException("#106 not supported sql : " + sql //
                + ", statement=" + statement + ", type=" + statement.getClass());
    }

    private JdbcStatement parseSelect(String sql, Statement statement) {
        Select select = (Select) statement;
        SelectBody selectBody = select.getSelectBody();

        if (selectBody instanceof PlainSelect) {
            return this.parsePlainSelect(sql, statement, selectBody);
        }

        throw new BudoJdbcDriverNotSupportedException("#102 not supported sql(selectBody) : sql=" + sql //
                + ", statement=" + statement + ", type=" + statement.getClass() //
                + ", selectBody=" + selectBody + ", selectBody.type=" + selectBody.getClass());

    }

    private JdbcStatement parsePlainSelect(String sql, Statement statement, SelectBody selectBody) {
        PlainSelect plainSelect = (PlainSelect) selectBody;

        if (MongoJSqlParserUtil.isCountStatement(plainSelect)) {
            return this.countStatement(sql, statement, plainSelect);
        }

        if (MongoJSqlParserUtil.isAggregateStatement(plainSelect)) {
            return this.aggregateStatement(sql, statement, plainSelect);
        }

        // 不是Find
        if (!MongoJSqlParserUtil.isFindStatement(plainSelect)) {
            throw new BudoJdbcDriverNotSupportedException("#149 not supported sql(plainSelect) : sql=" + sql + ", statement=" + statement //
                    + ", type=" + statement.getClass() + ", selectBody=" + selectBody + ", selectBody.type=" + selectBody.getClass());
        }

        // 是Find有子查询
        if (MongoJSqlParserUtil.hasSubSelect(plainSelect)) {
            return this.evalFindStatement(sql, (Select) statement, plainSelect); // 子查询,通过临时表实现
        }

        // 是Find无子查询
        return this.findStatement(sql, (Select) statement, plainSelect);
    }

    /**
     * @see org.budo.mongo.jdbc.driver.parser.jsqlparser.MongoJSqlParserUtil#isAggregateStatement(PlainSelect)
     */
    private JdbcStatement aggregateStatement(String sql, Statement statement, PlainSelect plainSelect) {
        List<Map<String, Object>> aggregatePipeline = new ArrayList<Map<String, Object>>();

        Function selectFunction = MongoJSqlParserUtil.getSelectFunction(plainSelect);
        Column parameterColumn = MongoJSqlParserUtil.getSelectFunctionParameter(plainSelect);
        String functionName = selectFunction.getName();
        String columnName = parameterColumn.getColumnName();

        // 筛选
        Map<String, Object> where = this.whereDocument(plainSelect.getWhere());
        if (null != where && !where.isEmpty()) {
            aggregatePipeline.add(MapUtil.stringObjectMap(MATCH, where));
        }

        // 计算
        Map<String, String> function = MapUtil.stringMap("$" + functionName.toLowerCase(), "$" + columnName);
        Map<String, Object> stringObjectMap = MapUtil.stringObjectMap(MongoStatement._ID, functionName, functionName + "(" + columnName + ")", function);
        aggregatePipeline.add(MapUtil.stringObjectMap(GROUP, stringObjectMap));

        Map<String, Object> statementMap = MapUtil.stringObjectMap(MongoStatement.AGGREGATE_PIPELINE, aggregatePipeline);
        String statementJson = JSON.serialize(statementMap);

        // 返回对象
        MongoAggregateStatement aggregateStatement = new MongoAggregateStatement();

        String collectionName = MongoJSqlParserUtil.getFromTable(statement).getName();
        aggregateStatement.setCollectionName(collectionName);
        aggregateStatement.setStatementJson(statementJson);

        // resultSetMetaData
        String[] selectColumnNames = MongoJSqlParserUtil.getSelectColumnNames(statement);
        BudoMongoJdbcResultSetMetaData resultSetMetaData = new BudoMongoJdbcResultSetMetaData(selectColumnNames, Types.BIGINT, Long.class);
        aggregateStatement.setResultSetMetaData(resultSetMetaData);

        aggregateStatement.setSql(sql);
        return aggregateStatement;
    }

    private JdbcStatement countStatement(String sql, Statement statement, PlainSelect plainSelect) {
        Map<String, Object> where = this.whereDocument(plainSelect.getWhere());

        Map<String, Object> statementMap = MapUtil.stringObjectMap(MongoStatement.WHERE, where);
        String statementJson = JSON.serialize(statementMap);

        // 返回对象
        MongoCountStatement countStatement = new MongoCountStatement();
        countStatement.setStatementJson(statementJson);

        String collectionName = MongoJSqlParserUtil.getFromTable(statement).getName();
        countStatement.setCollectionName(collectionName);

        // resultSetMetaData
        String[] selectColumnNames = MongoJSqlParserUtil.getSelectColumnNames(statement);
        BudoMongoJdbcResultSetMetaData resultSetMetaData = new BudoMongoJdbcResultSetMetaData(selectColumnNames, Types.BIGINT, Long.class);
        countStatement.setResultSetMetaData(resultSetMetaData);

        countStatement.setSql(sql);
        return countStatement;
    }

    private JdbcStatement parseDelete(String sql, Delete delete) {
        if (null != delete.getLimit()) {
            throw new RuntimeException("#241 not supported limit when delete, sql=" + sql);
        }

        if (null != delete.getOrderByElements()) {
            throw new RuntimeException("#245 not supported order by when delete, sql=" + sql);
        }

        String collectionName = delete.getTable().getName();

        Map<String, Object> where = this.whereDocument(delete.getWhere());

        Map<String, Object> statementMap = MapUtil.stringObjectMap(MongoStatement.WHERE, where);
        String statementJson = JSON.serialize(statementMap);

        // 返回对象
        MongoDeleteStatement deleteStatement = new MongoDeleteStatement();
        deleteStatement.setCollectionName(collectionName);
        deleteStatement.setStatementJson(statementJson);
        deleteStatement.setSql(sql);
        return deleteStatement;
    }

    private JdbcStatement parseUpdate(String sql, Update update) {
        if (null != update.getLimit()) {
            throw new RuntimeException("#265 not supported limit when update, sql=" + sql);
        }

        if (null != update.getOrderByElements()) {
            throw new RuntimeException("#269 not supported order by when update, sql=" + sql);
        }

        Table table = MongoJSqlParserUtil.getUpdateTable(sql, update);
        String collectionName = table.getName();

        List<Column> columns = update.getColumns();
        List<Expression> expressions = update.getExpressions();
        Map<String, Object> setDocument = this.valueDocument(columns, expressions);

        Map<String, Object> whereDocument = this.whereDocument(update.getWhere());

        Map<String, Object> statementMap = MapUtil.stringObjectMap(MongoStatement.WHERE, whereDocument, //
                MongoStatement.UPDATE_VALUES, MapUtil.stringObjectMap(SET, setDocument));
        String statementJson = JSON.serialize(statementMap);

        MongoUpdateStatement updateStatement = new MongoUpdateStatement();
        updateStatement.setStatementJson(statementJson);
        updateStatement.setCollectionName(collectionName);
        updateStatement.setSql(sql);
        return updateStatement;
    }

    private JdbcStatement parseInsert(String sql, Insert insert) {
        List<Map<String, Object>> insertDocuments = this.insertDocuments(insert);

        Map<String, Object> statementDocument = new HashMap<String, Object>();
        statementDocument.put(MongoStatement.INSERT_VALUES, insertDocuments);
        String statementJson = JSON.serialize(statementDocument);

        MongoInsertStatement insertStatement = new MongoInsertStatement();

        insertStatement.setCollectionName(insert.getTable().getName());
        insertStatement.setStatementJson(statementJson);

        insertStatement.setSql(sql);
        return insertStatement;
    }

    /**
     * 用临时表实现子查询
     */
    private JdbcStatement evalFindStatement(String sql, Select statement, PlainSelect plainSelect) {
        String script = new String();
        String tmpTableName = null;

        FromItem fromItem = plainSelect.getFromItem();

        while (true) {
            SubSelect subSelect = (SubSelect) fromItem;

            PlainSelect subBody = (PlainSelect) subSelect.getSelectBody();
            FromItem subFrom = subBody.getFromItem();

            // 临时表
            if (subFrom instanceof Table) {
                Table table = (Table) subFrom;
                String subTable = table.getName();
                Map<String, Object> subWhere = this.whereDocument(subBody.getWhere());

                Map<String, Object> orderBy = this.orderByDocument(subBody.getOrderByElements());
                Map<String, Object> pageDocument = this.limitDocument(subBody.getLimit());

                String findQuery = "db['" + subTable + "'].find(" + JSON.serialize(subWhere) + ")";
                if (null != orderBy && !orderBy.isEmpty()) {
                    findQuery += ".sort( " + JSON.serialize(orderBy) + " )";
                }

                if (null != pageDocument) {
                    Object skip = pageDocument.get(MongoStatement.SKIP);
                    if (null != skip && !"".equals(skip)) {
                        findQuery += ".skip( " + skip + " )"; // 这里加空格，statementJsonReplaceParameterValues 可能有问题
                    }
                    Object limit = pageDocument.get(MongoStatement.LIMIT);
                    if (null != limit && !"".equals(limit)) {
                        findQuery += ".limit( " + limit + " )";
                    }
                }

                tmpTableName = table.getName() + "_" + UuidUtil.randomUuid(); // 临时表
                script += "db['" + tmpTableName + "'].drop();"; // 删除临时表
                script += "copyToTable(" + findQuery + ", '" + tmpTableName + "');"; // 子查询，将数据存入临时表
                break; // 子查询的from是表是表
            }

            // 还是子查询
            if (subFrom instanceof SubSelect) {
                fromItem = subFrom; // 继续循环
                continue;
            }

            throw new RuntimeException("#331 type is " + subFrom.getClass() + ", subFrom=" + subFrom);
        }

        // 返回查询对象
        MongoEvalFindStatement mongoEvalFindStatement = new MongoEvalFindStatement();
        Map<String, Object> whereDocument = this.whereDocument(plainSelect.getWhere()); // 最外层的where

        Map<String, Object> orderBy = this.orderByDocument(plainSelect.getOrderByElements());
        Map<String, Object> pageDocument = this.limitDocument(plainSelect.getLimit());

        // 组装临时脚本
        String findQuery = "db['" + tmpTableName + "'].find(" + JSON.serialize(whereDocument) + ")";
        if (null != orderBy && !orderBy.isEmpty()) {
            findQuery += ".sort( " + JSON.serialize(orderBy) + " )";
        }

        if (null != pageDocument) {
            Object skip = pageDocument.get(MongoStatement.SKIP);
            if (null != skip && !"".equals(skip)) {
                findQuery += ".skip( " + skip + " )";
            }
            Object limit = pageDocument.get(MongoStatement.LIMIT);
            if (null != limit && !"".equals(limit)) {
                findQuery += ".limit( " + limit + " )";
            }
        }

        script += "return cursorToArray(" + findQuery + ");"; // 从临时表中查询返回数据

        mongoEvalFindStatement.setStatementJson(script);
        return mongoEvalFindStatement;
    }

    private JdbcStatement findStatement(String sql, Select select, PlainSelect plainSelect) {
        Table fromTable = MongoJSqlParserUtil.getFromTable(select);
        String collectionName = MongoJSqlParserUtil.getTableName(fromTable);

        Map<String, Object> statementDocument = new HashMap<String, Object>();

        Map<String, Object> where = this.whereDocument(plainSelect.getWhere());
        statementDocument.put(MongoStatement.WHERE, where);
        statementDocument.put(MongoStatement.SORT, this.orderByDocument(plainSelect.getOrderByElements()));
        statementDocument.put(MongoStatement.PAGE, this.limitDocument(plainSelect.getLimit()));

        String statementJson = JSON.serialize(statementDocument);

        MongoFindStatement findStatement = new MongoFindStatement();
        findStatement.setStatementJson(statementJson);

        findStatement.setCollectionName(collectionName);

        String[] selectColumnNames = MongoJSqlParserUtil.getSelectColumnNames(select);
        BudoMongoJdbcResultSetMetaData resultSetMetaData = new BudoMongoJdbcResultSetMetaData(selectColumnNames, Types.CHAR, Object.class);
        findStatement.setResultSetMetaData(resultSetMetaData);

        findStatement.setSql(sql);

        return findStatement;
    }

    private Map<String, Object> whereDocument(Expression where) {
        if (null == where) {
            return null;
        }

        // 比较
        if (where instanceof ComparisonOperator) {
            return this.comparisonOperator((ComparisonOperator) where);
        }

        if (where instanceof LikeExpression) {
            return this.likeExpression((LikeExpression) where);
        }

        // 括号
        if (where instanceof Parenthesis) {
            Parenthesis parenthesis = (Parenthesis) where;
            return this.whereDocument(parenthesis.getExpression());
        }

        if (where instanceof InExpression) {
            return this.inExpression((InExpression) where);
        }

        // AND OR
        if (where instanceof BinaryExpression) {
            return this.binaryExpression((BinaryExpression) where);
        }

        // IS NULL // IS NOT NULL
        if (where instanceof IsNullExpression) {
            return this.isNullExpression((IsNullExpression) where);
        }

        // where true
        throw new BudoJdbcDriverNotSupportedException("#464 where=" + where + ", type=" + where.getClass());
    }

    private Map<String, Object> binaryExpression(BinaryExpression binaryExpression) {
        Map<String, Object> left = this.whereDocument(binaryExpression.getLeftExpression());
        Map<String, Object> right = this.whereDocument(binaryExpression.getRightExpression());

        List<Map<String, Object>> valueList = ListUtil.toList(right, left);

        if (binaryExpression instanceof AndExpression) {
            return MapUtil.stringObjectMap(QueryOperators.AND, valueList);
        }

        if (binaryExpression instanceof OrExpression) {
            return MapUtil.stringObjectMap(QueryOperators.OR, valueList);
        }

        throw new RuntimeException("#419 binaryExpression=" + binaryExpression);
    }

    private Map<String, Object> inExpression(InExpression inExpression) {
        Expression leftExpression = inExpression.getLeftExpression();
        if (!(leftExpression instanceof Column)) {
            throw new BudoJdbcDriverNotSupportedException("#418 leftExpression=" + leftExpression + ", type=" + leftExpression.getClass());
        }

        Column column = (Column) leftExpression;

        ItemsList itemsList = inExpression.getRightItemsList();
        if (!(itemsList instanceof ExpressionList)) {
            throw new BudoJdbcDriverNotSupportedException("#434 itemsList=" + itemsList + ", type=" + itemsList.getClass());
        }

        List<Object> valueList = new ArrayList<Object>();
        ExpressionList expressionList = (ExpressionList) itemsList;
        List<Expression> expressions = expressionList.getExpressions();

        for (Expression expression : expressions) {
            Object value = this.parameterValue(expression);
            valueList.add(value);
        }

        String operator = inExpression.isNot() ? QueryOperators.NIN : QueryOperators.IN;
        return MapUtil.stringObjectMap(column.getColumnName(), //
                MapUtil.stringObjectMap(operator, valueList));
    }

    private Map<String, Object> likeExpression(LikeExpression likeExpression) {
        Expression leftExpression = likeExpression.getLeftExpression();
        Expression rightExpression = likeExpression.getRightExpression();

        String fieldName = MongoJSqlParserUtil.getColumnName(leftExpression);
        Object value = this.parameterValue(rightExpression);

        Map<String, Object> like = MapUtil.stringObjectMap(REGEX, value);

        // LIKE
        if (!likeExpression.isNot()) {
            return MapUtil.stringObjectMap(fieldName, like);
        }

        // NOT LIKE
        Map<String, Object> notLike = MapUtil.stringObjectMap(QueryOperators.NOT, like);
        return MapUtil.stringObjectMap(fieldName, notLike);
    }

    private Map<String, Object> isNullExpression(IsNullExpression isNullExpression) {
        Expression leftExpression = isNullExpression.getLeftExpression();
        String fieldName = MongoJSqlParserUtil.getColumnName(leftExpression);

        // IS NULL
        if (!isNullExpression.isNot()) {
            return MapUtil.stringObjectMap(fieldName, null);
        }

        // IS NOT NULL
        return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.NE, null));
    }

    private Map<String, Object> comparisonOperator(ComparisonOperator comparisonOperator) {
        Expression leftExpression = comparisonOperator.getLeftExpression();
        Expression rightExpression = comparisonOperator.getRightExpression();

        String fieldName = MongoJSqlParserUtil.getColumnName(leftExpression);
        Object value = this.parameterValue(rightExpression);

        if (comparisonOperator instanceof EqualsTo) {
            return MapUtil.stringObjectMap(fieldName, value);
        }

        if (comparisonOperator instanceof NotEqualsTo) {
            return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.NE, value));
        }

        if (comparisonOperator instanceof GreaterThan) {
            return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.GT, value));
        }

        if (comparisonOperator instanceof MinorThan) {
            return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.LT, value));
        }

        if (comparisonOperator instanceof GreaterThanEquals) {
            return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.GTE, value));
        }

        if (comparisonOperator instanceof MinorThanEquals) {
            return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.LTE, value));
        }

        throw new BudoJdbcDriverNotSupportedException("#477 comparisonOperator=" + comparisonOperator + ", type=" + comparisonOperator.getClass());
    }
}
